/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.mop.dinnerbook.dao;

import com.mop.dinnerbook.bean.Department;
import com.mop.dinnerbook.bean.Floor;
import com.mop.dinnerbook.bean.Member;
import com.mop.dinnerbook.bean.Project;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 *成员数据库操作业务类
 * @author Administrator
 */
public class MemberDAO extends BaseDAO{
    /*
     获取所有的有效的项目组成员
     */
    public List getAllMember(){
        List result = null;
        String sql="select pid,pname,remark from project order by pid";
        try {
			result = executeSelectList(sql, new Processor(){
				public Object product(ResultSet rst) throws SQLException{
					Project project = new Project();
					project.setPid(rst.getInt(1));
					project.setPname(rst.getString(2));
					project.setRemark(rst.getString(3));
					return project;
				}
			});
			Iterator iter = result.iterator();
			while(iter.hasNext()){
				Project project =(Project) iter.next();
				project.setMembers(getMembersByPid(project.getPid()));
			}
		} catch (SQLException e) {
                        System.out.println(sql);
			e.printStackTrace();
		}
        return result;
    }
    /*
     根据项目组的ID获取有效的成员
     */
    public List getMembersByPid(int pid){
        List result = null;
        String sql="select a.*,b.dname,c.fname from (select a.mid,a.did,a.mname,ifnull(b.checked,0) checked,a.remark,b.actionIp,a.fid from member a left outer join (select * from record where rdate >= curdate()) b on a.mid = b.mid where a.valid = 1 and a.pid = "+pid+" order by a.mid) a,department b,floors c where a.did=b.did and c.fid = a.fid";
        try {
			result = executeSelectList(sql, new Processor(){
				public Object product(ResultSet rst) throws SQLException{
					Member member = new Member();
                                        Department depart = new Department();
                                        Floor floor = new Floor();
					member.setMid(rst.getInt(1));
                                        depart.setDid(rst.getInt(2));
					member.setMname(rst.getString(3));
					member.setChecked(rst.getInt(4));
					member.setRemark(rst.getString(5));
                                        member.setActionIp(rst.getString(6));
                                        floor.setFid(rst.getInt(7));
                                        depart.setDname(rst.getString(8));
                                        member.setDepartment(depart);
                                        floor.setFname(rst.getString(9));
                                        member.setFloor(floor);
					return member;
				}
			});
		} catch (SQLException e) {
                        System.out.println(sql);
			e.printStackTrace();
		}
        return result;
    }
    /*
     获取所有的订餐成员
     */
    public List getAllBookedMember(){
        List result = null;
        String sql="select pid,pname,remark from project order by pid";
        try {
			result = executeSelectList(sql, new Processor(){
				public Object product(ResultSet rst) throws SQLException{
					Project project = new Project();
					project.setPid(rst.getInt(1));
					project.setPname(rst.getString(2));
					project.setRemark(rst.getString(3));
					return project;
				}
			});
			Iterator iter = result.iterator();
			while(iter.hasNext()){
				Project project =(Project) iter.next();
				project.setMembers(getBookedMembersByPid(project.getPid()));
			}
		} catch (SQLException e) {
                        System.out.println(sql);
			e.printStackTrace();
		}
        return result;
    }
    /*
     根据项目组ID获取该项目组内所有当天的订餐成员
     */
    public List getBookedMembersByPid(int pid){
        List result = null;
        String sql="select a.*,b.dname,c.fname from (select a.mid,a.did,a.mname,a.remark,b.actionIp,a.fid from member a , (select * from record where rdate >= curdate()) b where a.valid = 1 and a.mid = b.mid and a.pid = "+pid+" order by a.mid) a,department b,floors c where a.did=b.did and checked = 1 and c.fid = a.fid";
        try {
                result = executeSelectList(sql, new Processor(){
                        @Override
                        public Object product(ResultSet rst) throws SQLException{
                                Member member = new Member();
                                Department depart = new Department();
                                Floor floor = new Floor();
                                member.setMid(rst.getInt(1));
                                depart.setDid(rst.getInt(2));
                                member.setMname(rst.getString(3));
                                member.setRemark(rst.getString(4));
                                member.setActionIp(rst.getString(5));
                                floor.setFid(rst.getInt(6));
                                depart.setDname(rst.getString(7));
                                member.setDepartment(depart);
                                floor.setFname(rst.getString(8));
                                member.setFloor(floor);
                                return member;
                        }
                });
        } catch (SQLException e) {
                System.out.println(sql);
                e.printStackTrace();
        }
        return result;
    }
    /*
     根据项目组ID和时间段获取所有的订餐数据
     */
    public List getBookedMembersByPidDate(int pid,Date begin,Date end){
        List result = null;
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
        String beginDate = format.format(begin);
        String endDate = format.format(end);
        String sql="select a.*,b.dname,c.fname from (select a.mid,a.did,a.mname,a.remark,b.rdate,b.actionIp,b.checked,a.fid from member a , (select * from record where rdate between '"+beginDate+"' and '"+ endDate +"') b where a.valid = 1 and a.mid = b.mid and a.pid = "+pid+" order by a.mid) a,department b,floors c where a.did=b.did and checked = 1 and c.fid = a.fid";
        try {
                result = executeSelectList(sql, new Processor(){
                        @Override
                        public Object product(ResultSet rst) throws SQLException{
                                Member member = new Member();
                                Department depart = new Department();
                                Floor floor = new Floor();
                                member.setMid(rst.getInt(1));
                                depart.setDid(rst.getInt(2));
                                member.setMname(rst.getString(3));
                                member.setRemark(rst.getString(4));
                                DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                try{
                                    member.setRdate(formatter.parse(rst.getString(5)));
                                }catch(ParseException ee){
                                    member.setRdate(Calendar.getInstance().getTime());
                                }
                                member.setActionIp(rst.getString(6));
                                floor.setFid(rst.getInt(8));
                                depart.setDname(rst.getString(9));
                                member.setDepartment(depart);
                                floor.setFname(rst.getString(10));
                                member.setFloor(floor);
                                return member;
                        }
                });
        } catch (SQLException e) {
                System.out.println(sql);
                e.printStackTrace();
        }
        return result;
    }
/*
 根据时间段获取所有的订餐记录
 */
    public List getAllBookedMemberByDate(Date begin, Date end) {
        List result = null;
        String sql="select pid,pname,remark from project order by pid";
        try {
                result = executeSelectList(sql, new Processor(){
                        @Override
                        public Object product(ResultSet rst) throws SQLException{
                                Project project = new Project();
                                project.setPid(rst.getInt(1));
                                project.setPname(rst.getString(2));
                                project.setRemark(rst.getString(3));
                                return project;
                        }
                });
                Iterator iter = result.iterator();
                while(iter.hasNext()){
                        Project project =(Project) iter.next();
                        project.setMembers(getBookedMembersByPidDate(project.getPid(),begin,end));
                }
        } catch (SQLException e) {
                System.out.println(sql);
                e.printStackTrace();
        }
        return result;
    }
}
